package com.whnk.attendance.service.impl;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.whnk.attendance.domain.MonthHumenStatis;
import com.whnk.attendance.mapper.MonthHumenStatisMapper;
import com.whnk.attendance.service.IMonthHumenStatisService;
import com.whnk.attendance.vo.MonthHumenListJsonVo;
import com.whnk.attendance.vo.MonthHumenQuery;
import com.whnk.attendance.vo.MonthHumenStatisResultVo;
import com.whnk.common.core.utils.DateUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.*;

/**
 * 每月统计（人数汇总）Service业务层处理
 *
 * @author whnk
 * @date 2021-11-17
 */
@Service
public class MonthHumenStatisServiceImpl implements IMonthHumenStatisService
{
    private static final Logger logger = LoggerFactory.getLogger(MonthHumenStatisServiceImpl.class);
    @Autowired
    private MonthHumenStatisMapper monthHumenStatisMapper;

    /**
     * 查询每月统计（人数汇总）
     *
     * @param humenStatisId 每月统计（人数汇总）主键
     * @return 每月统计（人数汇总）
     */
    @Override
    public MonthHumenStatis selectMonthHumenStatisByHumenStatisId(Integer humenStatisId)
    {
        return monthHumenStatisMapper.selectMonthHumenStatisByHumenStatisId(humenStatisId);
    }

    /**
     * 查询每月统计（人数汇总）列表
     *
     * @param monthHumenStatis 每月统计（人数汇总）
     * @return 每月统计（人数汇总）
     */
    @Override
    public List<MonthHumenStatis> selectMonthHumenStatisList(MonthHumenStatis monthHumenStatis)
    {
        return monthHumenStatisMapper.selectMonthHumenStatisList(monthHumenStatis);
    }

    /**
     * 新增每月统计（人数汇总）
     *
     * @param monthHumenStatis 每月统计（人数汇总）
     * @return 结果
     */
    @Override
    public int insertMonthHumenStatis(MonthHumenStatis monthHumenStatis)
    {
        monthHumenStatis.setCreateTime(DateUtils.getNowDate());
        return monthHumenStatisMapper.insertMonthHumenStatis(monthHumenStatis);
    }

    /**
     * 修改每月统计（人数汇总）
     *
     * @param monthHumenStatis 每月统计（人数汇总）
     * @return 结果
     */
    @Override
    public int updateMonthHumenStatis(MonthHumenStatis monthHumenStatis)
    {
        monthHumenStatis.setUpdateTime(DateUtils.getNowDate());
        return monthHumenStatisMapper.updateMonthHumenStatis(monthHumenStatis);
    }

    /**
     * 批量删除每月统计（人数汇总）
     *
     * @param humenStatisIds 需要删除的每月统计（人数汇总）主键
     * @return 结果
     */
    @Override
    public int deleteMonthHumenStatisByHumenStatisIds(Integer[] humenStatisIds)
    {
        return monthHumenStatisMapper.deleteMonthHumenStatisByHumenStatisIds(humenStatisIds);
    }

    /**
     * 删除每月统计（人数汇总）信息
     *
     * @param humenStatisId 每月统计（人数汇总）主键
     * @return 结果
     */
    @Override
    public int deleteMonthHumenStatisByHumenStatisId(Integer humenStatisId)
    {
        return monthHumenStatisMapper.deleteMonthHumenStatisByHumenStatisId(humenStatisId);
    }

    @Override
    public MonthHumenListJsonVo queryMonthStatis(MonthHumenQuery monthHumenQuery) {
        MonthHumenListJsonVo monthHumenListJsonVo = new MonthHumenListJsonVo();
        JSONArray tableHead = new JSONArray();
        JSONArray tableData = new JSONArray();
        JSONArray tableMerge = new JSONArray();
        monthHumenListJsonVo.setTableHead(tableHead);
        monthHumenListJsonVo.setTableData(tableData);
        monthHumenListJsonVo.setTableMerge(tableMerge);
        JSONObject headRow1= new JSONObject(true);
        headRow1.put("columnName", "areaName");
        headRow1.put("columnComment", "交付");

        JSONObject headRow2= new JSONObject(true);
        headRow2.put("columnName", "proClaName");
        headRow2.put("columnComment", "项目分类");
        tableHead.add(headRow1);
        tableHead.add(headRow2);
        /**
         * 查询考勤表pm_sign_info,统计交付区域,项目分类,考勤时间,人数
         * 3	建行武数	0001	开发测试	2021-10	297
         * 3	建行武数	0001	开发测试	2021-11	307
         * 3	建行武数	0002	运维监控	2021-10	23
         * 3	建行武数	0002	运维监控	2021-11	23
         */
        List<MonthHumenStatisResultVo> signStatisResultVos = monthHumenStatisMapper.querySignStatisResult(monthHumenQuery);

        /**
         * 查询考勤表pm_sign_info,人员基本信息emp_base_info,
         * 查询统计交付区域,项目分类,考勤时间,缺勤人数
         * 3	建行武数	0001	开发测试	2021-10	29
         * 3	建行武数	0001	开发测试	2021-11	30
         * 3	建行武数	0002	运维监控	2021-10	2
         * 3	建行武数	0002	运维监控	2021-11	2
         */
        List<MonthHumenStatisResultVo> empStatisResultVos = monthHumenStatisMapper.queryEmpStatisResult(monthHumenQuery);
        /**
         * 特殊处理：
         * 1-12统计，人员A 5月份离职
         * 统计数为：1-4月份，正常，5月份，人员A数量不统计，6-12正常
         * key:areaName+proClaName+signDt,value:countNum
         */
        Map<String,Integer> departMap = new LinkedHashMap<>();
        if(!CollectionUtils.isEmpty(empStatisResultVos)){
            for(MonthHumenStatisResultVo monthHumenStatisResultVo : empStatisResultVos){
                departMap.put(monthHumenStatisResultVo.getAreaName()
                        +monthHumenStatisResultVo.getProClaName()+monthHumenStatisResultVo.getSignDt(), monthHumenStatisResultVo.getCountNum());
            }
        }

        /**
         * 区域,项目分类,考勤日期,数量统计明细map
         * (areaName:(proClaName:(signDt:countNum)))
         */
        Map<String,Map<String,Map<String,Integer>>> signDetailMap = new LinkedHashMap<>();

        /**
         * 区域,考勤日期,数量统计小计map
         * (areaName:(signDt:countNum))
         */
        Map<String,Map<String,Integer>> signXiaoJiMap = new LinkedHashMap<>();

        /**
         * 考勤日期,数量统计总计map
         * (signDt:countNum)
         */
        Map<String,Integer> signZongJiMap = new LinkedHashMap<>();

        Set<String> monthSet = new LinkedHashSet<>();
        for(MonthHumenStatisResultVo monthHumenStatisResultVo : signStatisResultVos){
            String signDt =  monthHumenStatisResultVo.getSignDt();
            if(!monthSet.contains(signDt)){
                monthSet.add(signDt);
            }
        }

        for(MonthHumenStatisResultVo monthHumenStatisResultVo : signStatisResultVos){
            String areaName = monthHumenStatisResultVo.getAreaName();
            String proClaName = monthHumenStatisResultVo.getProClaName();
            String signDt =  monthHumenStatisResultVo.getSignDt();
            Integer countNum = monthHumenStatisResultVo.getCountNum();
            String matchKey = areaName+proClaName+signDt;
            Integer departCount = departMap.get(matchKey);
            if(departCount != null){
                countNum = countNum - departCount;
            }
            Map<String,Map<String,Integer>> signDetailAreaMap = signDetailMap.get(areaName);
            if(CollectionUtils.isEmpty(signDetailAreaMap)){
                signDetailAreaMap = new LinkedHashMap<>();
                Map<String,Integer> signDetailProNameMap = new LinkedHashMap<>();
                signDetailProNameMap.put(signDt, countNum);
                signDetailAreaMap.put(proClaName,signDetailProNameMap);
                signDetailMap.put(areaName, signDetailAreaMap);
            }else{
                Map<String,Integer> signDetailProNameMap = signDetailAreaMap.get(proClaName);
                if(CollectionUtils.isEmpty(signDetailProNameMap)){
                    signDetailProNameMap = new LinkedHashMap<>();
                    signDetailProNameMap.put(signDt, countNum);
                    signDetailAreaMap.put(proClaName,signDetailProNameMap);
                }else{
                    signDetailProNameMap.put(signDt, countNum);
                }
            }

            Map<String,Integer> signDetailProNameMap = signXiaoJiMap.get(areaName);
            if(CollectionUtils.isEmpty(signDetailProNameMap)){
                signDetailProNameMap = new LinkedHashMap<>();
                signDetailProNameMap.put(signDt, countNum);
                signXiaoJiMap.put(areaName, signDetailProNameMap);
            }else{
                Integer count = signDetailProNameMap.get(signDt);
                if(null == count){
                    signDetailProNameMap.put(signDt, countNum);
                }else{
                    signDetailProNameMap.put(signDt, count+countNum);
                }

            }

            Integer count = signZongJiMap.get(signDt);
            if(null == count){
                signZongJiMap.put(signDt, countNum);
            }else{
                signZongJiMap.put(signDt, count+countNum);
            }

        }
        for(String str : monthSet){
            JSONObject jsonObject= new JSONObject(true);
            jsonObject.put("columnName", str);
            jsonObject.put("columnComment", str);
            tableHead.add(jsonObject);
        }

        /**
         *  统计合并的第一列map
         */
        Map<String,Integer> mergeStartMap = new LinkedHashMap<>();
        Map<String,Integer> mergeEndMap = new LinkedHashMap<>();
        int countStart = 0;
        for(Map.Entry<String,Map<String,Integer>> entry:signXiaoJiMap.entrySet()){
            String areaName = entry.getKey();
            Integer mergeStart = mergeStartMap.get(areaName);
            if(null == mergeStart){
                mergeStartMap.put(areaName, countStart);
            }
            Map<String,Integer> signDetailProNameMap = entry.getValue();
            Map<String,Map<String,Integer>> detailMap = signDetailMap.get(areaName);
            if(detailMap != null){
                for(Map.Entry<String,Map<String,Integer>> detailEntry:detailMap.entrySet()){
                    String proName = detailEntry.getKey();
                    Map<String,Integer> timeMap = detailEntry.getValue();
                    JSONObject dataRow= new JSONObject(true);
                    dataRow.put("areaName", areaName);
                    dataRow.put("proClaName", proName);
                    countStart++;
                    if(timeMap != null){
                        for(String month :monthSet){
                            Integer value = timeMap.get(month);
                            if(null == value){
                                dataRow.put(month, 0);
                            }else{
                                dataRow.put(month, value);
                            }
                        }
                    }
                    tableData.add(dataRow);
                }
            }
            JSONObject dataRow= new JSONObject(true);
            dataRow.put("areaName", areaName);
            dataRow.put("proClaName", "小计");
            if(signDetailProNameMap != null){
                for(String month :monthSet){
                    Integer value = signDetailProNameMap.get(month);
                    if(null == value){
                        dataRow.put(month, 0);
                    }else{
                        dataRow.put(month, value);
                    }
                }
            }
            tableData.add(dataRow);
            Integer mergeEnd = mergeEndMap.get(areaName);
            if(null == mergeEnd){
                mergeEndMap.put(areaName, countStart);
            }
            countStart++;
        }
        JSONObject dataRow= new JSONObject(true);
        dataRow.put("areaName", "总计");
        dataRow.put("proClaName", "");
        for(Map.Entry<String,Integer> timeMapEntry:signZongJiMap.entrySet()) {
            dataRow.put(timeMapEntry.getKey(), timeMapEntry.getValue());
        }
        tableData.add(dataRow);
        for(Map.Entry<String,Integer> mergeStartEntry: mergeStartMap.entrySet()){
            JSONObject dataRowMerge= new JSONObject(true);
            dataRowMerge.put("areaName", mergeStartEntry.getKey());
            dataRowMerge.put("start", mergeStartEntry.getValue());
            dataRowMerge.put("end", mergeEndMap.get(mergeStartEntry.getKey()));
            tableMerge.add(dataRowMerge);
        }
        return monthHumenListJsonVo;
    }

    @Override
    public void exportExcel(HttpServletResponse response, MonthHumenListJsonVo monthHumenListJsonVo) throws IOException {
        JSONArray tableHead = monthHumenListJsonVo.getTableHead();
        JSONArray tableData= monthHumenListJsonVo.getTableData();
        JSONArray tableMerge = monthHumenListJsonVo.getTableMerge();
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Disposition", "attachment; filename=\"export.xlsx\"");
        // 定义一个工作薄（所有要写入excel的数据，都将保存在workbook中）
        XSSFWorkbook workbook = new XSSFWorkbook();

        Map<String, CellStyle> styles = createStyles(workbook);
        // 创建一个sheet
        XSSFSheet sheet = workbook.createSheet();
        XSSFRow head = sheet.createRow(0);
        for (int i = 0; i < tableHead.size(); i++) {
            JSONObject obj = (JSONObject)tableHead.get(i);
            Set<Map.Entry<String, Object>> entrySet = obj.entrySet();
            int j =0;
            for (Map.Entry<String, Object> entry : entrySet) {
                if(1==j){
                    XSSFCell xssfCell = head.createCell(i);
                    xssfCell.setCellStyle(styles.get("header"));
                    xssfCell.setCellValue(entry.getValue().toString());
                    break;
                }
                j++;
            }
        }

        for (int i = 0; i < tableData.size(); i++) {
            XSSFRow row = sheet.createRow(i+1);
            JSONObject obj = (JSONObject)tableData.get(i);
            Set<Map.Entry<String, Object>> entrySet = obj.entrySet();
            int j =0;
            for (Map.Entry<String, Object> entry : entrySet) {
                Object value = entry.getValue();
                XSSFCell xssfCell = row.createCell(j);
                xssfCell.setCellStyle(styles.get("data"));
                if(value instanceof String){
                    xssfCell.setCellValue((String)entry.getValue());
                }else if(value instanceof Integer){
                    xssfCell.setCellValue((Integer)entry.getValue());
                }else{
                    xssfCell.setCellValue(entry.getValue().toString());
                }
                j++;
            }
        }

        for (int i = 0; i < tableMerge.size(); i++) {
            JSONObject obj = (JSONObject)tableMerge.get(i);
            int startRow = obj.getInteger("start");
            int lastRow = obj.getInteger("end");
            /*
             * 设定合并单元格区域范围
             * 	firstRow  0-based
             * 	lastRow   0-based
             * 	firstCol  0-based
             * 	lastCol   0-based
             */
            CellRangeAddress cra=new CellRangeAddress(startRow+1, lastRow+1, 0, 0);

            //在sheet里增加合并单元格
            sheet.addMergedRegion(cra);

        }

        try
        {
            workbook.write(response.getOutputStream());
        }
        catch (Exception e)
        {
            logger.error("导出Excel异常{}", e);
        }
        finally
        {
            IOUtils.closeQuietly(workbook);
            IOUtils.closeQuietly(response.getOutputStream());
        }

    }

    private Map<String, CellStyle> createStyles(Workbook wb)
    {
        // 写入各条记录,每条记录对应excel表中的一行
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        Font dataFont = wb.createFont();
        dataFont.setFontName("Arial");
        dataFont.setFontHeightInPoints((short) 10);
        style.setFont(dataFont);
        styles.put("data", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font headerFont = wb.createFont();
        headerFont.setFontName("Arial");
        headerFont.setFontHeightInPoints((short) 10);
        headerFont.setBold(true);
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(headerFont);
        styles.put("header", style);

        style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        Font totalFont = wb.createFont();
        totalFont.setFontName("Arial");
        totalFont.setFontHeightInPoints((short) 10);
        style.setFont(totalFont);
        styles.put("total", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.LEFT);
        styles.put("data1", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);
        styles.put("data2", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.RIGHT);
        styles.put("data3", style);

        return styles;
    }
}
